﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;

namespace Dan.Common.Extend
{
    public static class DapperExtend
    {
        /// <summary>
        /// Gets the page.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="orderbyfield">The orderby filed.</param>
        /// <param name="direction">0 des 1 asc</param>
        /// <param name="pageIndex">Index of the page.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <returns></returns>
        public static string GetPage(this string sql, string orderbyfield, int direction, int pageIndex, int pageSize)
        {
            if (!string.IsNullOrWhiteSpace(orderbyfield))
            {
                if (orderbyfield.Trim().Contains(' '))
                {
                    throw new ArgumentException("orderbyfield only one word", nameof(orderbyfield));
                }

                sql += $" order by {orderbyfield.Trim()} ";

                var str = direction == 0 ? "desc" : "asc";
                sql += $"{str}";
            }

            if (pageIndex < 0)
            {
                throw new ArgumentException("pageindex <0", nameof(pageIndex));
            }

            if (pageSize <= 0)
            {
                throw new ArgumentException("pagesize <=0", nameof(pageSize));
            }

            sql += $" limit {pageIndex * pageSize},{pageSize}";
            return sql;
        }

        /// <summary>
        /// 如果是leftjoin的话,需要使用group by,否则统计的总数是错误的
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int GetTotalCount(this IDbConnection conn, string sql, object param = null)
        {
            return conn.Query<int>(sql, param).FirstOrDefault();
        }

        public static int GetTotalCount2(this IDbConnection conn, string sql, object param = null)
        {
            var sql2 = $@"select count(*) from ({sql})a";
            return conn.Query<int>(sql2, param).FirstOrDefault();
        }

        /// <summary>
        /// Gets the bool in SQL.
        /// </summary>
        /// <param name="flag">if set to <c>true</c> [flag].</param>
        /// <returns></returns>
        private static int GetBoolInSql(bool flag)
        {
            return flag ? 1 : 0;
        }

        /// <summary>
        /// 将单引号,斜杠转义,将bool转义1,0 ,将datetime转义成yyyy-MM-dd HH:mm:ss时间格式
        /// </summary>
        /// <param name="para">参数</param>
        /// <returns></returns>
        public static string GetStrInSql(this object para)
        {
            if (para is bool)
            {
                return GetBoolInSql((bool) para).ToString();
            }

            if (para is DateTime)
            {
                if ((DateTime) para == DateTime.MinValue)
                {
                    throw new ArgumentException("equals datetime minvalue", nameof(para));
                }

                return para.SafeToDateTime().ToString("yyyy-MM-dd HH:mm:ss");
            }

            var pa = para.SafeToString();

            var str = pa.Replace(@"\", @"\\")
                .Replace("'", "\\'");

            return str;
        }

        /// <summary>
        /// <para>Generates the insert SQL with auto ignore primarykey.</para>
        /// <para>when primary key is int less than 0 then ignore or primary key is string isnullempty then ignore</para>
        /// <para>will only generate sql both in columns and t.propertys</para>
        /// 
        /// <para>test with 4 types(columns,property,tablename,primarykey)</para>
        /// <para>边界值分析</para>
        /// <para>(1.columns property  相等,多,少)</para>
        /// <para>(2.tablename null,"",!=typename )</para>
        /// <para>(3.primarykey null,"",!="default value id")</para>
        /// <para>(4.primary key value = default ,ingore insert sql)</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columnStr">The table column string.</param>
        /// <param name="t">The t.</param>
        /// <param name="tablename">The tablename.</param>
        /// <param name="key">The Primary key,will auto ignore by value</param>
        /// <param name="ignoreColumns">ingore columns</param>
        /// <returns></returns>
        public static string GenerateInsertSql<T>(this string columnStr, T t, string tablename = null,
            string key = "id",List<string> ignoreColumns = null) where T : class
        {
            List<string> ignoreList = new List<string>();
            if (ignoreColumns != null)
            {
                ignoreColumns.ForEach(s => s.ToLowerInvariant());  // to lower
                ignoreList.AddRange(ignoreColumns);
            }
            
            var type = t.GetType();

            if (string.IsNullOrWhiteSpace(tablename))
            {
                tablename = type.Name.ToLower();
                if (tablename.Contains("anonymoustype"))
                {
                    throw new ArgumentException("invalid table name", nameof(tablename));
                }

                if (tablename.StartsWith("po"))
                {
                    tablename = tablename.TrimStart("po".ToCharArray());
                }
            }

            if (string.IsNullOrWhiteSpace(key))
            {
                key = "id";
            }

            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            var columns = columnStr.SafeToString().ToLower().SafeSplit(",").ToList();

            var props = type.GetProperties();

            foreach (var p in props)
            {
                var v = p.GetValue(t);
                var pname = p.Name.ToLower();

                if (ignoreList.Contains(pname))
                {
                    continue;
                }

                if (columns.Contains(pname)) // exist in table
                {
                    if (pname.ToLower() == key.ToLower()) // ignore key when key's value is empty or 0
                    {
                        #region  ignore primary key when int <=0 or string isnullempty

                        if (v is int)
                        {
                            if ((int) v <= 0)
                            {
                                continue;
                            }
                        }
                        else if (v is string)
                        {
                            if (string.IsNullOrEmpty((string) v))
                            {
                                continue;
                            }
                        }

                        #endregion
                    }

                    if (pname.ToLower() == "createdate")
                    {
                        p.SetValue(t, DateTime.Now);
                    }

                    // add to sb
                    sb.Append($"{pname},");
                    sb2.Append($"@{pname},");
                }
            }

            var sql = sb.ToString().TrimEnd(',');
            var sql2 = sb2.ToString().TrimEnd(',');
            string result = $"insert {tablename}({sql})values({sql2});SELECT @@IDENTITY;";
            return result;
        }

        /// <summary>
        /// <para>generate Updates sql the ignore null.</para>
        /// 
        /// <para>test with 4 types(columns,property,tablename,primarykey)</para>
        /// <para>边界值分析</para>
        /// <para>(1.columns property  相等,多,少)</para>
        /// <para>(2.tablename null,"",!=typename )</para>
        /// <para>(3.primarykey null,"",!="default value id")</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columnStr">The column string.</param>
        /// <param name="t">The t.</param>
        /// <param name="tablename">The tablename.</param>
        /// <param name="key">The key.</param>
        /// <param name="ignoreColumns">ingore columns(主键并不会被忽略)</param>
        /// <returns></returns>
        public static string GenerateUpdateSql<T>(this string columnStr, T t, string tablename = null,
            string key = "id",List<string> ignoreColumns = null)
        {
            List<string> ignoreList = new List<string> {"id", "createdate"};
            if (ignoreColumns != null)
            {
                ignoreColumns.ForEach(s => s.ToLowerInvariant());  // to lower
                ignoreList.AddRange(ignoreColumns);
            }
            
            var type = t.GetType();

            if (string.IsNullOrWhiteSpace(tablename))
            {
                tablename = type.Name.ToLowerInvariant();
                if (tablename.Contains("anonymoustype"))
                {
                    throw new ArgumentException("invalid table name", nameof(tablename));
                }

                if (tablename.StartsWith("po"))
                {
                    tablename = tablename.TrimStart("po".ToCharArray());
                }
            }

            if (string.IsNullOrWhiteSpace(key))
            {
                throw new ArgumentNullException(nameof(key));
            }


            StringBuilder sb = new StringBuilder();
            var columns = columnStr.SafeToString().ToLowerInvariant().SafeSplit(",");

            if (!columns.Contains(key.ToLowerInvariant()))
            {
                throw new ArgumentException("primary key not in columns", nameof(key));
            }

            var props = type.GetProperties();

            foreach (var p in props)
            {
                var v = p.GetValue(t);
                var pname = p.Name.ToLowerInvariant();

                if (pname.ToLower() == key.ToLower())
                {
                    continue;
                }

                // 两种情况不修改,一种是null,还有一种是datetime.minvalue
                if (v != null) // can be 0,but not 
                {
                    if (v is DateTime) // datetime can't be minvalue
                    {
                        if ((DateTime) v != DateTime.MinValue)
                        {
                            if (columns.Contains(p.Name.ToLower())) // exist in table
                            {
                                if (!ignoreList.Contains(pname.ToLower())) // ignore key
                                {
                                    // add to sb
                                    sb.Append($" {pname}=@{pname},");
                                }
                            }
                        }
                    }
                    else
                    {
                        if (columns.Contains(p.Name.ToLower())) // exist in table
                        {
                            if (!ignoreList.Contains(pname.ToLower())) // ignore key
                            {
                                // add to sb
                                sb.Append($"{pname}=@{pname},");
                            }
                        }
                    }
                }
            }

            var sql = sb.ToString().TrimEnd(',');
            string result = $"update {tablename} set {sql} where {key}=@{key}";
            return result;
        }

        public static string GenerateDeleteSql(this string tablename, string key = "id")
        {
            if (string.IsNullOrWhiteSpace(key))
            {
                throw new ArgumentNullException(nameof(key));
            }

            return $@"delete from {tablename} where {key}=@{key}";
        }
    }
}